package net.sf.rose.file.excel.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import net.sf.rose.file.excel.poi.message.CellMessage;
import net.sf.rose.file.excel.poi.message.RowMessage;
import net.sf.rose.file.excel.poi.message.SheetMessage;
import net.sf.rose.file.excel.poi.model.BookModel;
import net.sf.rose.file.excel.poi.model.CellModel;
import net.sf.rose.file.excel.poi.model.SheetModel;
import net.sf.rose.jdbc.PageBean;
import net.sf.rose.util.BeanWrapper;
import net.sf.rose.util.DataUtils;
import net.sf.rose.util.RoseRuntimeException;

/**
 * @author fengjian
 * @since 2018/12/19
 */
public class Excel2003 extends ExcelUtils {

	private static Logger LOG = Logger.getLogger(Excel2003.class);

	/** 工作薄模板 */
	private BookModel bookModelBean = new BookModel();

	/** 模板工作薄控件 */
	private HSSFWorkbook modelBook = null;

	/** 读入工作薄控件 */
	private HSSFWorkbook readBook = null;

	/** 当前编辑的表格控件 */
	HSSFSheet currentSheet = null;

	/** 当前编辑的表格模板 */
	SheetModel currentSheetModel = null;

	/**
	 * 功能：设置Excel表格的数据模板
	 * 
	 * @param model 数据模板
	 */
	@Override
	public void addSheetModel(SheetModel model) {
		bookModelBean.addSheetModel(model);
	}

	/**
	 * 功能：设置Excel模板文件
	 * 
	 * @param template 模板文件
	 * @return 成功返回true，否则false
	 */
	@Override
	@SuppressWarnings("deprecation")
	public boolean setBookModelFile(File template) {
		InputStream in = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		SheetModel sheetModel = null;
		CellModel cellModel = null;
		try {
			in = new FileInputStream(template);
			modelBook = new HSSFWorkbook(new POIFSFileSystem(in));
			// 遍历每个表格
			for (int i = 0; i < modelBook.getNumberOfSheets(); i++) {
				sheet = modelBook.getSheetAt(i);
				sheetModel = new SheetModel();
				sheetModel.setName(sheet.getSheetName());
				// 遍历每一行
				for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
					row = sheet.getRow(j);
					if (row == null) {
						continue;
					}
					// 遍历每一单元格
					for (short k = 0; k < row.getLastCellNum(); k++) {
						// System.out.println(j + ":" + k + ":" +
						cell = row.getCell(k);
						if (cell == null) {
							continue;
						}
						// 如果单元格数据类型为非字符串格式，那么肯定不需要格式化处理
						if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
							continue;
						}
						// 取得格式化字符串
						String str = cell.getStringCellValue();
						if (str.equals("")) {
							continue;
						}
						if (str.indexOf("${") != -1) {
							cellModel = new CellModel();
							cellModel.setRow(j);
							cellModel.setCol(k);
							cellModel.setContent(str);
							sheetModel.addCellModel(cellModel);
						} else if (str.indexOf("$[") != -1) {
							cellModel = new CellModel();
							cellModel.setRow(j);
							cellModel.setCol(k);
							cellModel.setContent(str);
							sheetModel.setListRowNum(j);
							sheetModel.setListColNum(k);
							sheetModel.addListCellModel(cellModel);
						}
					}
				}
				bookModelBean.addSheetModel(sheetModel);
			}
			currentSheet = modelBook.getSheetAt(0);
			currentSheetModel = bookModelBean.getSheetModel(0);
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
			return false;
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}

	/**
	 * 功能：读取要导入数据的源文件
	 * 
	 * @param in 源文件流
	 * @return 成功返回true，否则false
	 */
	public boolean readFile(InputStream in) {
		try {
			readBook = new HSSFWorkbook(new POIFSFileSystem(in));
			currentSheet = readBook.getSheetAt(0);
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
			return false;
		} finally {
			if (in != null) {
				try {
					readBook.close();
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}

	/**
	 * 功能：从Excel表中提取实体对象的属性值
	 * 
	 * @param bean - 要加载属性的实体对象
	 */
	public void setCurrentSheetName(String sheetName) {
		currentSheet = readBook.getSheet(sheetName);
		currentSheetModel = bookModelBean.getSheetModel(sheetName);
	}

	/**
	 * 功能：从Excel表中提取实体对象的属性值
	 * 
	 * @param bean - 要加载属性的实体对象
	 */
	public void getDataBean(Object bean) {
		HSSFCell cell = null;
		// 遍历每个表格
		List<CellModel> cellModels = currentSheetModel.getCellModels();
		for (CellModel model : cellModels) {
			cell = currentSheet.getRow(model.getRow()).getCell(model.getCol());
			if (cell == null) {
				continue;
			}
			model.getCellValue(cell, bean);
		}
	}

	/**
	 * 功能：从Excel表中提取实体对象的属性值数组
	 * 
	 * @param clazz - 要加载属性的类
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> getListBean(Class<?> clazz) {
		HSSFRow row = null;
		HSSFCell cell = null;
		List<T> list = new ArrayList<>();
		List<CellModel> listCellModels = currentSheetModel.getListCellModels();
		if (listCellModels.size() == 0) {
			return list;
		}
		T data = null;
		try {
			for (int j = currentSheetModel.getListRowNum(); j <= currentSheet.getLastRowNum(); j++) {
				row = currentSheet.getRow(j);
				data = (T) clazz.newInstance();
				// 遍历每一单元格
				for (int k = 0; k < listCellModels.size(); k++) {
					CellModel cm = listCellModels.get(k);
					cell = row.getCell(cm.getCol());
					if (cell != null) {
						cm.getCellValue(cell, data);
					}
				}
				list.add(data);
			}
		} catch (Exception e) {
			throw new RoseRuntimeException(e);
		}
		return list;
	}

	/**
	 * 功能：根据提供的Excel模板文件和要导入数据的源文件，读取数据
	 * 
	 * @param in  源文件流
	 * @param map 加载的对象集合
	 * @return 成功返回true，否则false
	 */
	@Override
	@SuppressWarnings("unchecked")
	public boolean read(InputStream in, Map<String, Object> map) {
		HSSFWorkbook readBook = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		SheetModel sheetModel = null;
		try {
			Object bean = null;
			String sheetName = null;
			BeanWrapper bw = new BeanWrapper();
			readBook = new HSSFWorkbook(new POIFSFileSystem(in));
			String listName = null; // 返回列表的变量名称
			String className = null; // 返回实体类名称
			boolean single = readBook.getNumberOfSheets() > 1 ? false : true;
			if (single) {
				Object obj = map.get("list_class");
				className = obj != null ? obj.toString() : null;
			}
			// 遍历每个表格
			for (int i = 0; i < readBook.getNumberOfSheets(); i++) {
				sheet = readBook.getSheetAt(i);
				sheetModel = bookModelBean.getSheetModel(i);
				sheetName = sheetModel.getName();
				bean = map.get(sheetName);
				if (bean == null) {
					bean = map;
				}
				List<CellModel> cellModels = sheetModel.getCellModels();
				for (CellModel model : cellModels) {
					cell = sheet.getRow(model.getRow()).getCell(model.getCol());
					if (cell == null) {
						continue;
					}
					model.getCellValue(cell, bean);
				}
				List<CellModel> listCellModels = sheetModel.getListCellModels();
				if (listCellModels.size() == 0) {
					continue;
				}
				// 列表的行数据，如果提供了实体类名装载就用实体类，如果没有就用Map装载
				Object data = null;
				boolean userfulRow = false;
				SheetMessage sheetMessage = new SheetMessage(); // 解析执行结果消息
				Object obj = map.get(i + "_list_class");
				className = obj != null ? obj.toString() : className; // 如果此sheet指定了封闭实体类名
				List<Object> list = new ArrayList<>();
				for (int j = sheetModel.getListRowNum(); j <= sheet.getLastRowNum(); j++) {
					row = sheet.getRow(j);
					if (className != null) {
						data = DataUtils.getInstance(this.getClass(), className.toString());
					} else {
						data = new HashMap<String, Object>();
					}
					// 遍历每一单元格
					userfulRow = false;
					RowMessage rowMessage = new RowMessage(); // 行解析执行结果消息
					for (int k = 0; k < listCellModels.size(); k++) {
						CellModel cm = listCellModels.get(k);
						cell = row.getCell(cm.getCol());
						if (cell != null) {
							// 一行记录中，只要有一个单元格数据有效，则这行记录有效
							PageBean page = cm.getCellValue(cell, data);
							if (page.isSuccess()) {
								userfulRow = true;
							} else {
								CellMessage cellMessage = new CellMessage(page);
								cellMessage.setRow(j);
								cellMessage.setCol(k);
								rowMessage.addMessage(cellMessage);
							}
							if (listName == null) {
								listName = cm.getMainName();
							}
						}
					}
					if (rowMessage.getSuccess() == 2) {
						sheetMessage.addErrorRowMessage(rowMessage);
					}
					// 如果这行记录，没有一个单元格有效，则从此行开始后面所有的记录全部放弃解析
					if (!userfulRow) {
						break;
					}
					sheetMessage.addSuccessCount();
					list.add(data);
				}
				// 从容器中取列表参数名，如果没有，就用默认名称list
				if (bean instanceof Map) {
					Map<String, Object> m = (Map<String, Object>) bean;
					// 如果是多个表，就用加序号前缀
					if (single) {
						m.put("list", list);
						m.put("message", sheetMessage);
					} else {
						m.put("list_" + i, list);
						m.put("message_" + i, sheetMessage);
					}
				} else {
					bw.setWrappedInstance(bean);
					bw.set(listName, list);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
			return false;
		} finally {
			if (in != null) {
				try {
					readBook.close();
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return true;
	}

	/**
	 * 功能：根据提供的数据和Excel模板向目标文件导出Excel
	 * 
	 * @param columnMap - 标题容器，Key为属性名，Value为单元格标题
	 */
	public void setDynamicColumns(Map<String, String> columnMap) {
		List<CellModel> listCellModels = currentSheetModel.getListCellModels();
		if (listCellModels.size() == 0) {
			return;
		}
		if (columnMap == null || columnMap.size() == 0) {
			return;
		}
		// 往右复制列
		int size = columnMap.size();
		int rowIndex = listCellModels.get(0).getRow();
		HSSFRow row = currentSheet.getRow(rowIndex - 1);
		CellModel lastCM = listCellModels.get(listCellModels.size() - 1);
		int colIndex = lastCM.getCol();
		copyCols(currentSheet, rowIndex - 1, colIndex, size);// 复制标题列的格式
		copyCols(currentSheet, rowIndex, colIndex, size);// 复制内容列的格式
		// 往右复制列宽
		for (int i = 1; i <= size; i++) {
			HSSFCell toCell = (HSSFCell) row.getCell(colIndex + i);
			currentSheet.setColumnWidth(toCell.getColumnIndex(), currentSheet.getColumnWidth(colIndex));
		}
		// 设置标题列内容和数据模型
		CellModel templet = listCellModels.get(listCellModels.size() - 1);
		HSSFRow titleRow = currentSheet.getRow(rowIndex - 1);
		int col = templet.getCol();
		for (String key : columnMap.keySet()) {
			col++;
			CellModel cm = templet.copy(key);
			cm.setCol(col);
			listCellModels.add(cm);
			titleRow.getCell(col).setCellValue(columnMap.get(key));
		}
	}

	/**
	 * 功能：将模板中的某一列替换成某一组数据
	 * 
	 * @param key       - 要替换的列名
	 * @param columnMap - 标题容器，Key为属性名，Value为单元格标题
	 */
	public void setDynamicColumns(String key, Map<String, String> columnMap) {
		HSSFRow row = null;
		List<CellModel> celllist = currentSheetModel.getListCellModels();
		for (int j = 0; j < celllist.size(); j++) {
			if (celllist.get(j).getSubName().equals(key)) {
				if (columnMap == null || columnMap.size() == 0) {
					// 删除列
					CellModel model = celllist.remove(j);
					row = currentSheet.getRow(model.getRow());
					// 将数据列全部左移一格
					shiftCells(modelBook, row, model.getCol(), -1, false);
					row.removeCell(row.getCell((int) row.getLastCellNum() - 1));
					for (int k = j; k < celllist.size(); k++) {
						CellModel cm = celllist.get(k);
						cm.setCol(cm.getCol() - 1);
					}
					// 将标题列全部左移一格
					row = currentSheet.getRow(model.getRow() - 1);
					shiftCells(modelBook, row, model.getCol() + 1, -1, true);
					// 移动复制列宽
					shiftSheetWidth(row, model.getCol(), -1);
					// 删除最后一个单元格
					row.removeCell(row.getCell((int) row.getLastCellNum() - 1));
				} else {
					Object[] fields = columnMap.keySet().toArray();
					String field = fields[0].toString();
					CellModel model = celllist.get(j);
					model.setSubName(field);// 替换属性
					row = currentSheet.getRow(model.getRow());
					HSSFRow titleRow = currentSheet.getRow(model.getRow() - 1);
					titleRow.getCell(model.getCol()).setCellValue(columnMap.get(field)); // 替换标题
					if (fields.length > 1) {
						int size = fields.length;
						int maxColIndex = celllist.get(celllist.size() - 1).getCol();
						CellModel currentCell = celllist.get(j);
						// 如果当前单元格右边还有单元格
						if (currentCell.getCol() < maxColIndex) {
							// 将原始表格右边部分往右移
							shiftCells(modelBook, titleRow, model.getCol(), size - 1, true);
							shiftCells(modelBook, row, model.getCol(), size - 1, true);
						}
						// 设置数据模型
						int col = model.getCol();
						for (int k = j + 1; k < celllist.size(); k++) {
							CellModel cm = celllist.get(k);
							cm.setCol(cm.getCol() + size - 1);
						}
						for (int k = 1; k < size; k++) {
							col++;
							field = fields[k].toString();
							CellModel cm = model.copy(field);
							cm.setCol(col);
							celllist.add(cm);
							titleRow.getCell(col).setCellValue(columnMap.get(field));
						}
						// 移动复制列宽
						shiftSheetWidth(row, model.getCol(), size - 1);
					}
				}
			}
		}
	}

	/**
	 * 功能：将数据写入当前的数据表格中
	 * 
	 * @param data - 要写入的数据容器集合，可以是对象或Map容器
	 */
	public void setData(Object data) {
		HSSFCell cell = null;
		List<CellModel> cellModels = currentSheetModel.getCellModels();
		for (CellModel model : cellModels) {
			cell = currentSheet.getRow(model.getRow()).getCell(model.getCol());
			if (cell == null) {
				continue;
			}
			model.setCellValue(cell, data);
		}
	}

	/**
	 * 功能：将列表数据写入当前的数据表格中
	 * 
	 * @param list - 要写入的数据列表
	 */
	public void setDataList(List<?> list) {
		HSSFRow row = null;
		HSSFCell cell = null;
		CellModel cellModel = null;
		List<CellModel> listCellModels = currentSheetModel.getListCellModels();
		if (listCellModels.size() != 0) {
			row = currentSheet.getRow(currentSheetModel.getListRowNum());
			if (list == null || list.size() == 0) {
				// 删除一行
				currentSheet.removeRow(row);
				return;
			}
			int size = list.size();
			int rowNum = currentSheetModel.getListRowNum();
			// 如果长度大于1，那么需要插入一定数量的行
			if (size > 1) {
				// 当前行之下的行往下移size-1行位置
				int rowSum = currentSheet.getLastRowNum();
				if (rowNum < rowSum) {
					currentSheet.shiftRows(rowNum + 1, rowSum, size - 1, true, false);
				}
				// 将当前行的内容格式插入下面size-1行
				Map<Integer, HSSFCellStyle> styleMap = new HashMap<>();
				for (int k = rowNum + 1; k < rowNum + size; k++) {
					HSSFRow targetRow = currentSheet.createRow(k);
					copyRow(modelBook, row, targetRow, styleMap, false);
				}
			}
			// 按行填充行数据
			for (int k = 0; k < size; k++) {
				Object data = list.get(k);
				row = currentSheet.getRow(rowNum);
				// 设置每个单元格
				for (int l = 0; l < listCellModels.size(); l++) {
					cellModel = listCellModels.get(l);
					cell = row.getCell(cellModel.getCol());
					cellModel.setCellValue(cell, data);
				}
				rowNum++;
			}
		}
	}

	/**
	 * 功能：生成目标Excel文件
	 * 
	 * @param out 导出的文件流
	 * @return 是否成功
	 */
	public boolean write(OutputStream out) {
		try {
			modelBook.write(out);
			modelBook.close();
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
			return false;
		}
		return true;
	}

	/**
	 * 功能：根据提供的数据和Excel模板向目标文件导出Excel
	 * 
	 * @param out 导出的文件流
	 * @param map 加载的对象集合
	 * @return 是否成功
	 */
	@SuppressWarnings("unchecked")
	public boolean write(OutputStream out, Map<String, Object> map) {
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		SheetModel sheetModel = null;
		CellModel cellModel = null;
		int rowNum = -1;
		Object bean = null;
		String sheetName = null;
		BeanWrapper bw = new BeanWrapper();
		for (int i = 0; i < bookModelBean.getSheetModels().size(); i++) {
			sheet = modelBook.getSheetAt(i);
			sheetModel = bookModelBean.getSheetModel(i);
			bean = map.get(sheetName);
			if (bean == null) {
				bean = map;
			}
			List<CellModel> cellModels = sheetModel.getCellModels();
			for (CellModel model : cellModels) {
				cell = sheet.getRow(model.getRow()).getCell(model.getCol());
				if (cell == null) {
					continue;
				}
				model.setCellValue(cell, bean);
			}
			List<CellModel> listCellModels = sheetModel.getListCellModels();
			if (listCellModels.size() != 0) {
				row = sheet.getRow(sheetModel.getListRowNum());
				String listName = listCellModels.get(0).getMainName();
				Object obj = null;
				if (!(bean instanceof Map)) {
					bw.setWrappedInstance(bean);
					obj = bw.get(listName);
				}
				if (obj == null) {
					obj = map.get(listName);
				}
				if (obj == null) {
					sheet.removeRow(row);
					continue;
				}
				List<Object> list = (List<Object>) obj;
				int size = list.size();
				if (size == 0) {
					// 删除一行
					sheet.removeRow(row);
					continue;
				}
				rowNum = sheetModel.getListRowNum();
				// 如果长度大于1，那么需要插入一定数量的行
				if (size > 1) {
					// 当前行之下的行往下移size-1行位置
					int rowSum = sheet.getLastRowNum();
					if (rowNum < rowSum) {
						sheet.shiftRows(rowNum + 1, rowSum, size - 1, true, false);
					}
					// 将当前行的内容格式插入下面size-1行
					Map<Integer, HSSFCellStyle> styleMap = new HashMap<>();
					for (int k = rowNum + 1; k < rowNum + size; k++) {
						HSSFRow targetRow = sheet.createRow(k);
						copyRow(modelBook, row, targetRow, styleMap, false);
					}
				}
				// 按行填充行数据
				for (int k = 0; k < size; k++) {
					Object data = list.get(k);
					row = sheet.getRow(rowNum);
					// 设置每个单元格
					for (int l = 0; l < listCellModels.size(); l++) {
						cellModel = listCellModels.get(l);
						cell = row.getCell(cellModel.getCol());
						cellModel.setCellValue(cell, data);
					}
					rowNum++;
				}
				continue;
			}
		}
		try {
			modelBook.write(out);
			modelBook.close();
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
			return false;
		}
		return true;
	}

	/**
	 * 行复制功能
	 * 
	 * @param wb            工作薄
	 * @param fromRow       源行
	 * @param toRow         目标行
	 * @param styleMap      样式
	 * @param copyValueFlag true则连同cell的内容一起复制
	 */
	public void copyRow(HSSFWorkbook wb, HSSFRow fromRow, HSSFRow toRow, Map<Integer, HSSFCellStyle> styleMap,
			boolean copyValueFlag) {
		for (int i = fromRow.getFirstCellNum(); i < fromRow.getLastCellNum(); i++) {
			HSSFCell tmpCell = (HSSFCell) fromRow.getCell(i);
			HSSFCell newCell = toRow.createCell(i);
			HSSFCellStyle style = styleMap.get(i);
			if (style == null) {
				style = wb.createCellStyle();
				copyCellStyle(tmpCell.getCellStyle(), style);
				styleMap.put(i, style);
			}
			copyCell(wb, tmpCell, newCell, style, copyValueFlag);
		}
	}

	/**
	 * 用指定的单元格向右复制并插入count列
	 * 
	 * @param startRow - 起始行号
	 * @param startCol - 起始列号
	 * @param count    - 要复制的列数
	 */
	public void copyCols(HSSFSheet currentSheet, int startRow, int startCol, int count) {
		HSSFWorkbook wb = currentSheet.getWorkbook();
		HSSFRow currentRow = currentSheet.getRow(startRow);
		// 当前列往右复制count列
		HSSFCell tmpCell = (HSSFCell) currentRow.getCell(startCol);
		HSSFCellStyle tmpStyle = tmpCell.getCellStyle();
		for (int i = startCol; i <= startCol + count; i++) {
			HSSFCell newCell = currentRow.createCell(i);
			HSSFCellStyle newStyle = wb.createCellStyle();
			copyCellStyle(tmpStyle, newStyle);
			copyCell(wb, tmpCell, newCell, newStyle, true);
		}
	}

	/**
	 * 移动单元格
	 * 
	 * @param wb            工作薄
	 * @param row           要移动的行
	 * @param index         要移动的起始位置
	 * @param n             要移动的单元格数，正数向右移，负数向左移
	 * @param copyValueFlag true则连同cell的内容一起复制
	 */
	public void shiftCells(HSSFWorkbook wb, HSSFRow row, int index, int n, boolean copyValue) {
		// 往左移
		if (n < 0) {
			for (int i = index; i < row.getLastCellNum(); i++) {
				HSSFCell fromCell = (HSSFCell) row.getCell(i);
				HSSFCell toCell = (HSSFCell) row.getCell(i + n);
				HSSFCellStyle style = toCell.getCellStyle();
				copyCellStyle(fromCell.getCellStyle(), style);
				copyCell(wb, fromCell, toCell, style, copyValue);
			}
		}
		// 往右移
		else {
			// 往右移
			HSSFCellStyle toStyle = null;
			for (int i = row.getLastCellNum() - 1; i > index; i--) {
				HSSFCell fromCell = (HSSFCell) row.getCell(i);
				HSSFCell toCell = (HSSFCell) row.getCell(i + n);
				if (toCell == null) {
					toCell = row.createCell(i + n);
				}
				toStyle = wb.createCellStyle();
				copyCellStyle(fromCell.getCellStyle(), toStyle);
				copyCell(wb, fromCell, toCell, toStyle, copyValue);
			}
			// 往右复制
			HSSFCell fromCell = (HSSFCell) row.getCell(index);
			for (int i = 1; i <= n; i++) {
				HSSFCell toCell = (HSSFCell) row.getCell(index + i);
				toStyle = wb.createCellStyle();
				copyCellStyle(fromCell.getCellStyle(), toStyle);
				copyCell(wb, fromCell, toCell, toStyle, copyValue);
			}
		}
	}

	/**
	 * 移动复制列宽
	 * 
	 * @param row   要移动的行
	 * @param index 要移动的起始位置
	 * @param n     要移动的单元格数，正数向右移动，负数向左移动
	 */
	public void shiftSheetWidth(HSSFRow row, int index, int n) {
		HSSFSheet sheet = row.getSheet();
		// 往左移动
		if (n < 0) {
			for (int i = row.getLastCellNum() - n - 1; i > index; i--) {
				HSSFCell fromCell = (HSSFCell) row.getCell(i);
				HSSFCell toCell = (HSSFCell) row.getCell(i + n);
				sheet.setColumnWidth(toCell.getColumnIndex(), sheet.getColumnWidth(fromCell.getColumnIndex()));
			}
		}
		// 往右移动
		else {
			for (int i = row.getLastCellNum() - 1; i > index; i--) {
				HSSFCell fromCell = (HSSFCell) row.getCell(i - n);
				HSSFCell toCell = (HSSFCell) row.getCell(i);
				sheet.setColumnWidth(toCell.getColumnIndex(), sheet.getColumnWidth(fromCell.getColumnIndex()));
			}
			HSSFCell fromCell = (HSSFCell) row.getCell(index);
			for (int i = 1; i < n; i++) {
				HSSFCell toCell = (HSSFCell) row.getCell(index + i);
				sheet.setColumnWidth(toCell.getColumnIndex(), sheet.getColumnWidth(fromCell.getColumnIndex()));
			}
		}
	}

	/**
	 * 单元格复制
	 * 
	 * @param wb            工作薄
	 * @param srcCell       源单元格
	 * @param destCell      目标单元格
	 * @param newstyle      复制样式
	 * @param copyValueFlag true则连同cell的内容一起复制
	 */
	private void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell destCell, HSSFCellStyle newstyle,
			boolean copyValueFlag) {
		// 样式
		destCell.setCellStyle(newstyle);
		// 评论
		if (srcCell.getCellComment() != null) {
			destCell.setCellComment(srcCell.getCellComment());
		}
		// 不同数据类型处理
		int srcCellType = srcCell.getCellType();
		destCell.setCellType(srcCellType);
		if (copyValueFlag) {
			if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
					destCell.setCellValue(srcCell.getDateCellValue());
				} else {
					destCell.setCellValue(srcCell.getNumericCellValue());
				}
			} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
				destCell.setCellValue(srcCell.getRichStringCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
			} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
				destCell.setCellValue(srcCell.getBooleanCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
				destCell.setCellErrorValue(srcCell.getErrorCellValue());
			} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
				destCell.setCellFormula(srcCell.getCellFormula());
			} else {
			}
		}
	}

	/**
	 * 功能：复制一个单元格样式到目的单元格样式
	 * 
	 * @param fromStyle 源单元格格式
	 * @param toStyle   目标单元格格式
	 */
	private void copyCellStyle(HSSFCellStyle fromStyle, HSSFCellStyle toStyle) {
		toStyle.setAlignment(fromStyle.getAlignment());
		// 边框和边框颜色
		toStyle.setBorderBottom(fromStyle.getBorderBottom());
		toStyle.setBorderLeft(fromStyle.getBorderLeft());
		toStyle.setBorderRight(fromStyle.getBorderRight());
		toStyle.setBorderTop(fromStyle.getBorderTop());
		toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
		toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
		toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
		toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
		// 背景和前景
		toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
		toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

		toStyle.setDataFormat(fromStyle.getDataFormat());
		toStyle.setFillPattern(fromStyle.getFillPattern());
		toStyle.setFont(fromStyle.getFont(modelBook));
		toStyle.setHidden(fromStyle.getHidden());
		toStyle.setIndention(fromStyle.getIndention());// 首行缩进
		toStyle.setLocked(fromStyle.getLocked());
		toStyle.setRotation(fromStyle.getRotation());// 旋转
		toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
		toStyle.setWrapText(fromStyle.getWrapText());
	}
}
